package csu.web.mypetstore.persistence.impl;

import csu.web.mypetstore.domain.Category;
import csu.web.mypetstore.domain.Product;
import csu.web.mypetstore.persistence.DBUtil;
import csu.web.mypetstore.persistence.ProductDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {
    String getProduct ="SELECT\n" +
            "      PRODUCTID," +
            "      NAME," +
            "      DESCN as description," +
            "      CATEGORY as categoryId" +
            "    FROM PRODUCT" +
            "    WHERE PRODUCTID = ?";
    String getProductListByCategory = "SELECT" +
            "      PRODUCTID," +
            "      NAME," +
            "      DESCN as description," +
            "      CATEGORY as categoryId" +
            "    FROM PRODUCT" +
            "    WHERE CATEGORY = ?";
    String searchProductList ="select" +
            "      PRODUCTID," +
            "      NAME," +
            "      DESCN as description," +
            "      CATEGORY as categoryId" +
            "    from PRODUCT" +
            "    WHERE lower(name) like ?";
    @Override
    public List<Product> getProductListByCategory(String categoryId) {
        List<Product> products = new ArrayList<Product>();
        try
        {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getProductListByCategory);
            preparedStatement.setString(1,categoryId);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Product product = new Product();
                product.setProductId((resultSet.getString(1)));
                product.setName(resultSet.getString(2));
                product.setCategoryId(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                products.add(product);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return products;
    }

    @Override
    public Product getProduct(String productId) {
        Product product = null;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(getProduct);
            preparedStatement.setString(1, productId);
            ResultSet resultSet =  preparedStatement.executeQuery();
            if (resultSet.next()){
                product = new Product();
                product.setProductId((resultSet.getString(1)));
                product.setName(resultSet.getString(2));
                product.setCategoryId(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e)
        {
            e.printStackTrace();
        }

        return product;
    }

    @Override
    public List<Product> searchProductList(String keywords) {
        List<Product> products = new ArrayList<Product>();
        try
        {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(searchProductList);
            preparedStatement.setString(1,keywords);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Product product = new Product();
                product.setProductId((resultSet.getString(1)));
                product.setName(resultSet.getString(2));
                product.setCategoryId(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                products.add(product);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return products;
    }
}
